Page 1 of 44

MET CS 669 Database Design and Implementation for Business

Term Project Iteration 6

Name: Edward Myers

Table of Contents

Project Direction Overview ............................................................................................................................2

Use Cases and Fields ......................................................................................................................................3

Structural Database Rules ..............................................................................................................................8

Conceptual Entity-Relationship Diagram .....................................................................................................17

Full DBMS Physical ERD ................................................................................................................................19

Stored Procedure Execution and Explanations ............................................................................................27

Question Identification and Explanations ....................................................................................................32

Query Executions and Explanations .............................................................................................................33

Index Identification and Creations ...............................................................................................................37

History Table Demonstration .......................................................................................................................40

Data Visualizations .......................................................................................................................................40

Summary and Reflection ..............................................................................................................................44

Page 2 of 44

Project Direction Overview

Modifications with justification between iterations are identified as red text.

I would like to create a database that tracks vehicle status, history and maintenance information for a

manager of a small vehicle fleet, called “FleetManager”. I work for a local township in Pennsylvania, and

for many years they have used an application that stores vehicle maintenance file-by-file in an operating

system, which works ok for keeping history records on a vehicle but doesn’t lend itself to generating

meaningful business analytics. When describing the user access below, I anticipate the mechanic user to

primarily use a desktop application tied to the database with intuitive option buttons made for ease-of-

use. Administrative users from a finance perspective may query the database more directly to gain

custom insight on trends and expenses.

For a mechanic user, the database would store maintenance transactions about a vehicle, whether it

needed grease/oil, any replacement parts, or got sent out to a vendor for repair. The mechanic would

start a maintenance entry in a desktop application linked to the database, add the appropriate details

about the work and subsequent parts and save it as an entry. The mechanic could also look at the

history of all work done on a particular vehicle sorted by date and pull general information about its

parts for ordering and inventory. If the mechanic needed to know what type of oil filter the vehicle

required, they would select general information on the desktop application which accesses the known

vehicle part data from the database.

For an administrative user, the database would yield long term data regarding a vehicle and its pattern

of maintenance. The finance clerk would query the database for information tying a vehicle to its year-

over-year expenditure to create a report on cost to maintain vs. replace. A fleet manager would query

the database for each vehicle’s mileage to identify patterns of uneven wear across the vehicle fleet. A

supervisor will query the database for a count and list of what vehicles each mechanic has worked on to

even out workload distribution.

I envision that the database will hold:

- General information about each individual vehicle

- Date, parts, cost and other details about each maintenance work order

- Parts inventory which associates which parts go to which vehicles, and how many are in stock.

My interest in the program comes from my desire to help out my home township. I wrote the program

that they currently use in Java nearly 20 years ago without any database background. I’m thrilled that

they still use the program, but in an era of integrated asset management applications they could be

getting so much more out of their data. To that end, I would like to at least design my idea of their next

step.

Page 3 of 44

Use Cases and Fields

Original Use Cases:

New Use Cases (Iteration 3)

Acknowledged Use Cases Outside

of Scope:

New Vehicle Entry

Appointment Entry

Workload Lookup

Edit Vehicle Entry

Expenditure Over Time

Add Maintenance Entry

Mileage Reporting

Add Part Entry

Add Vendor

The following are use cases that could be expanded upon that share a similarity to an already-defined

use case:

Edit Maintenance Entry

Delete Maintenance Entry

Edit Part Entry

Delete Part Entry

Edit Vendor

Delete Vendor

Add Mechanic

Delete Vehicle Entry (removed from original use cases)

From feedback, the ‘Delete Vehicle Entry’ has been removed as the use case implies that the vehicle and

its attributes have already been added into the database. As we are working with fresh data for

create/edit transactions, it has been omitted. In its place is a new use case: ‘Appointment Entry’.

New Vehicle Entry & Setup Use Case

1) The mechanic clicks the ‘Add Vehicle’ button on the application interface.

2) The application provides a fill form to accept the data fields.

3) The mechanic specifies the type of vehicle as CDL vehicle, standard vehicle, accessory, or

none.

4) The mechanic enters all other the appropriate information and clicks ‘Save’.

Field

What it Stores

Why it’s Needed

vehicle_ID

The 4-digit departmental

ID code of the vehicle

This is the naming convention already used, and

uniquely identifies the vehicle.

make

The brand of the vehicle

Only certain parts work on certain vehicles.

model

The model of the vehicle

Only certain parts work on certain vehicles.

license

The license plate value

You can check which vehicles need renewed registration

and pull status of insurance.

VIN

The auto manufacturer

vehicle identification

number

The coding of the VIN gives exact vehicle specifications,

and the VIN is required on nearly all types of

registration paperwork.

year

The creation year of the

vehicle

The year of the vehicle is the primary statistic used to

determine parts availability and to evaluate

replacement. The local township participates in a fleet

lease program in which a mixed trade in of vehicles 3

years and older reduce the overall fleet age to save on

maintenance costs.

Page 4 of 44

inspection_date

The date the vehicle is

due for its next

inspection.

The inspection date is important to keep the vehicle

street-legal, and serves as a parameter by which the

application’s appointment notifier communicates a

necessary vehicle maintenance activity to the mechanic.

vehicle_type

The type of vehicle,

being a CDL vehicle,

standard vehicle,

accessory, or none.

This is a new attribute created for the

generalization/specialization rule set. Depending on the

type of vehicle, different information will be available

and different check flags will apply to appointments.

Notes:

- Vehicle_ID is already established per vehicle as part of a departmental code. (Ex.: all

administrative vehicles are ‘1xxx’, police ‘2xxx’, wastewater ‘3xxx’, etc.)

Edit Vehicle Entry

1) The mechanic clicks the ‘Edit Vehicle’ button on the application interface.

2) The application displays a list of current vehicles and prompts the mechanic for the ID of the

vehicle to edit.

3) The mechanic enters the ID of the vehicle and presses ‘OK’.

4) The application displays a fill form screen with the current vehicle data populated.

5) The mechanic uses the fill form to edit the populated data as desired.

6) The mechanic presses ‘Save’, applying the changes to the vehicle instance.

Field

What it Stores

Why it’s Needed

vehicle_ID

The 4-digit departmental

ID code of the vehicle

This is the naming convention already used, and uniquely

identifies the vehicle. Requested to determine what

vehicle’s information to extract from the database.

make

The brand of the vehicle

Only certain parts work on certain vehicles. Field that is

extracted from the database and may be edited by user.

model

The model of the vehicle

Only certain parts work on certain vehicles. Field that is

extracted from the database and may be edited by user.

license

The license plate value

You can check which vehicles need renewed registration

and pull status of insurance. Field that is extracted from

the database and may be edited by user.

VIN

The auto manufacturer

vehicle identification

number

The coding of the VIN gives exact vehicle specifications,

and the VIN is required on nearly all types of registration

paperwork. Field that is extracted from the database

and may be edited by user.

year

The creation year of the

vehicle

The year of the vehicle is the primary statistic used to

determine parts availability and to evaluate

replacement. The local township participates in a fleet

lease program in which a mixed trade in of vehicles 3

years and older reduce the overall fleet age to save on

maintenance costs. Field that is extracted from the

database and may be edited by user.

Page 5 of 44

Add Maintenance Entry

1) The mechanic clicks the ‘Add Maintenance’ button on the application interface.

2) The application provides a fill form to accept information regarding the maintenance.

3) The mechanic selects the vehicle being maintained from a list.

4) The mechanic enters the data about the maintenance, including information about the

mechanic, type of maintenance, date, etc. as outlined below.

5) The mechanic enters information about used parts & materials in a sub-window.

6) To add parts used to the maintenance window, the mechanic clicks on the 'include parts'

button.

7) The mechanic selects the part by name from a drop-down list or enters the part ID.

8) The application queries the parts inventory table to display the full part ID, name, and

description of part.

9) The mechanic enters the number/quantity of the product used.

10) The application references the part record from the inventory again to subtract used quantity

from stock quantity. If there is not enough quantity identified in stock, an alert flashes on the

screen giving the mechanic the ability to return and change or override and continue.

11) The mechanic presses ‘Ok’ in the part window, navigating back to the maintenance window.

12) The mechanic presses ‘Save’ to save the maintenance transaction.

Field

What it Stores

Why it’s Needed

mechanic_name

The mechanic’s name

This helps to track which mechanic performed

what work for workload analysis.

type

Type of maintenance

performed

Classifies the type of maintenance performed.

Commonly this field distinguishes if the

maintenance was major, minor, vendor or an

inspection.

vehicle_ID

The 4-digit departmental ID

code of the vehicle

This number identifies which vehicle received

the maintenance.

date

The date the maintenance

was performed.

This is useful for tracking when maintenance was

last completed and the overall frequency of

maintenance.

cost

The total cost of the repair

considering all fluids, parts,

etc.

This is useful for tracking overall maintenance

costs for the vehicle. I’ve disassociated

individual cost of parts as that would require the

mechanic to constantly update the parts

inventory with current pricing.

mileage

Current mileage of the

vehicle at time of

maintenance

This is useful for comparison with other

maintenance records to determine overall miles

traveled within a given time frame.

vendor_name

The name of a vendor which

performs the repair if

maintenance is outsourced

This is useful to compare what vendors get the

most business and also to compare how much

maintenance vendors do to mechanics in-house.

part_ID

The application’s part or

product identifier

This number is used to reference the

application's unique identifier for the part. This

is assigned by the program upon a part entry

into the parts inventory.

Page 6 of 44

part_quantity_used

The number or quantity of

the part or product used

during the maintenance

This number is referenced against the database’s

known quantity of the item in question. If more

of the item is used than the database knows is

within possession, a flag is triggered to the user.

Add Part Entry

*Note: The scope of Add Part now removes the ability to create a new part from the Add Maintenance

window. All creating and editing of parts is performed from the Parts Inventory window of the

application.

1) If a mechanic user would like to create part information, they click the 'parts inventory'

button on the application interface.

2) The mechanics clicks 'add part'.

3) The application provides a fill form to accept information regarding the part.

4) The mechanic enters the data about the part, including ID, name, serial #, etc. by text

input as outlined below.

5) The mechanic presses 'save'.

6) The application checks to make sure no duplicate part ID’s exist, after which the program

saves the new part into the appropriate database table.

Field

What it Stores

Why it’s Needed

part_name

The parts’s name

Quick reference or jargon related to the

identification of what the part is used for.

part_serial

The specific manufacturer’s

part or product identifier

This number is used to reference the

manufacturer’s specification on how and where

the part or product can be used.

description

Additional notes on the part

or product

If the identification or use of the part or product

is not clear from the name or researching the

serial #, then additional notes to describe the part

and its use are helpful.

quantity_in_stock

The quantity of the part on

hand.

This is useful for determining when to order more

supply. This value is also compared to quantities

deducted during an Add Maintenance event.

Add Vendor Use Case

1) The mechanic clicks the ‘Add Vendor’ button on the application interface.

2) The application provides a fill form to accept information regarding the vendor.

3) The mechanic enters the data about the vendor.

4) The mechanic presses ‘Save’ to save the entry.

Field

What it Stores

Why it’s Needed

vendor_name

The vendor’s name

This helps to track which vendor performed

what work for workload analysis.

Page 7 of 44

vendor_address

(street_number,

name, town,

zipcode)

Address variables

This helps identify where the vendor is located.

Proximity can be a factor for vendor repair

selection.

phone_number

(area_code)

Phone number variables

This stores telephone contact information for

the vendor.

agent_name

Vendor representative, if

applicable

This is useful in case the vendor has an area

sales representative that receives business from

your area.

Email contact address

Similar to phone number, it is another way to

get in contact with the vendor.

Appointment Entry Use Case

The application has a feature that notifies the mechanic of a scheduled maintenance by either an

automatic parameter (ex./ the application triggers its own required maintenance alert if the current date

is within 3 months of a vehicle’s inspection deadline), or by manual parameter where the mechanic has

entered a ‘Scheduler Entry’ vehicle appointment. For manual entry, the mechanic does the following:

1) The mechanic clicks a button on the application interface called “New Appointment”.

2) A fill form screen appears for the mechanic to enter details about the appointment.

3) The form asks the mechanic to select the vehicle by its vehicle ID code from a drop menu.

4) The form asks for the mechanic to be assigned the maintenance activity from a drop menu.

5) The form requires a maintenance due date to be filled in, as well as a checkbox to select if the

mechanic would like a one-week reminder sent to them.

6) The form allows for the mechanic to put in a note regarding useful details about the

maintenance, such as type, ordered parts, etc.

This use case as well as the ‘Maintenance Entry’ use case identifies that a ‘mechanic’ data entity will

need to be created. Not only will a mechanic’s first name, last name, certified license, etc. be needed,

but phone number, email, or preferred method of contact so that the application can send the mechanic

a message required by the fleet scheduler.

Field

What it Stores

Why it’s Needed

appointment_date

The desired date of the

upcoming maintenance

This is the primary variable of the fleet

scheduler, communicating what vehicles need

maintenance attention to specific mechanics.

mechanic name

(fname & lname

menu selection)

The first and last name of the

mechanic

Even though this information is pulled from the

known registry of mechanics, the selected

mechanic name is attached to the selected

appointment record. That mechanic is now

assigned the task.

vehicle_id

The 4-digit departmental ID

code of the vehicle

This number identifies which vehicle received

the maintenance. Even though it is pulled from

the registry of vehicle, upon creation of this

appointment record the vehicle gets assigned

the pending maintenance.

Page 8 of 44

Part History Use Case

The application will have a section where the mechanic will be able to generate custom reports that are

useful to the maintenance of the vehicle fleet. One of these reports relates to the usage frequency of all

parts, which can give valuable insight into wear patterns and restock frequency. The mechanic should

be able to generate this report through the following steps:

1) The mechanic clicks the ‘Reports’ button on the application.

2) The mechanic uses a scroll menu to view the available reports. To pull a part usage history, the

mechanic would select ‘Parts Usage Report’.

3) The report prompt asks the mechanic to enter a start and end date for the reporting bounds.

Alternatively, the mechanic could select ‘All’ which would print the entire usage history.

4) The application generates the report, converting it into a digital file and uses the workstation

operating system’s default file manager options to save it in the desired location.

For each part usage record entry, the program will need to monitor the Part entity already established in

the database with some sort of trigger that fires when a change happens to the part inventory. The

part_name is necessary attribute to explain what part is experiencing the change. Presumably the

quantity is changing, therefore what the quantity used to be and what the quantity now is needs to be

recorded. Lastly, the other data makes no sense without the date in which the change occurred.

Field

What it Stores

Why it’s Needed

part_name

The parts’s name

Quick reference or jargon related to the

identification of what the part is used for.

part_old_quantity

The previous quantity of the

part

This is half of the reason that the change existed.

Gives what the quantity used to be.

part_new_quantity

The new quantity of the part

This is the other half of the reason that the

change existed. Gives what the quantity has

become.

change_date

The date of the change

Gives the time dimension to the change in

quantity. The other variables do not make sense

without a timeframe to describe them.

Page 9 of 44

Structural Database Rules

Associative Database Rules

Use Case #1: New Vehicle Entry

- The mechanic clicks the ‘Add Vehicle’ button on the application interface.

- The application provides a fill form to accept the data fields.

- The mechanic enters all the appropriate information and clicks ‘Save’.

From the above use case, I identify a ‘mechanic’ and data about a ‘vehicle’. I know that there is an

interaction between the two entities at some point, but this is an interaction between a mechanic and a

third-party program which accesses the database. The mechanic is an obvious entity, as is vehicle.

Regarding vehicle, the following two attributes can warrant their own entities:

Field

What it Stores

make

The brand of the vehicle

model

The model of the vehicle

In a local municipality’s fleet, often the manager or mayor makes the decision to acquire vehicles of the

same type or similar enough that they take a lot of common parts. This cuts down on the cost of

keeping repair materials in stock, and most of the vehicle’s maintenance timings and needs will line up.

So lets look at the relationship between vehicle, make and model. A unique relationship exists between

vehicle/make and vehicle/model. While an argument can be made that make (1->M) model (1->M)

vehicle relationships occur as I used in my previous iteration, a simpler way is to show that a vehicle

must have a unique make and a unique model, while a make may produce many vehicles and many

vehicles may exist of a certain model. The two modified rules are:

Use Case #2 does not tell us any more about the relationships. Moving on…

Use Case #3: Add Maintenance Entry

- The mechanic clicks the ‘Add Maintenance’ button on the application interface.

…

- The mechanic selects the vehicle being maintained from a list.

…

- The mechanic enters information about used parts & materials in a sub-window.

Entity #1

Participation

Relationship

Plurality

Entity #2

A vehicle make

May

Apply to

Many

Vehicles

A vehicle

Must

Have

One

Vehicle make

Entity #1

Participation

Relationship

Plurality

Entity #2

A vehicle model

May

Correspond to

Many

Vehicles

A vehicle

Must

Correspond to

One

Vehicle model

Page 10 of 44

A few relationships are now revealed. Now we know that Mechanics perform a maintenance activity on

vehicles, which they record as a maintenance entry in the program. One mechanic enters data about a

particular maintenance activity that they performed, but multiple maintenance activities can be

performed by a mechanic. If a maintenance activity exists, it had to be performed by either a mechanic

or a vendor (further ahead). A mechanic does not necessarily have to perform a maintenance activity.

*Note: my program purposely limits the performance of one maintenance or repair to one mechanic.

We know from the steps in the program that the mechanic is forced to select one vehicle to log the

maintenance activity toward. Thus, every maintenance activity maps to only one vehicle. It follows that

a maintenance activity cannot exist without a vehicle identified to work on. Also, it makes sense that a

vehicle can have multiple maintenance activities performed on it over time, or none.

As part of the maintenance entry into the application, the mechanic enters what parts were used during

the maintenance activity. Depending on the type of maintenance, it is possible for no parts, one type, or

multiple types of parts to be used. Conversely, a part may be included in none, one or many

maintenance activities.

Use Case #4: Add Part Entry

- If a mechanic user would like to create part information, they click the 'parts inventory' button

on the application interface.

…

- The mechanic enters the data about the part, including ID, name, serial #, etc. by text input as

outlined below.

…

- The application checks to make sure no duplicate part ID’s exist, after which the program saves

the new part into the appropriate database table.

Entity #1

Participation

Relationship

Plurality

Entity #2

A mechanic

May

Perform

Many

Maintenance

Activities

A maintenance

activity

May

Be performed by

One

mechanic

Entity #1

Participation

Relationship

Plurality

Entity #2

A maintenance

activity

Must

Apply to

One

Vehicle

A vehicle

may

Have

Many

Maintenance

activities

Entity #1

Participation

Relationship

Plurality

Entity #2

A maintenance

activity

May

Use

Many

Parts

A part

May

Be used in

Many

Maintenance

activities

Page 11 of 44

This information, like Use Case 1, gives us the foundation of more entity tables. The third-party

application uses a construct called ‘Parts Inventory’, which calls on a table of parts with the database to

add, edit and delete from. However, we can infer that these parts are inventoried for a reason: that

they fit into vehicles. A vehicle can have a lot of parts, and some parts can fit into multiple vehicles. For

the purposes of the program, it would not make sense to force populating all the parts of a vehicle into

the database as there would be thousands, most of which would never receive maintenance. Likewise,

we would not include all possible vehicles that the part applied to since most of them are not in the

vehicle fleet.

Use Case #5: Add Vendor

- The application provides a fill form to accept information regarding the vendor.

- The mechanic enters the data about the vendor.

Although the use case does not specifically say what the vendor does or why we are recording data

about vendors, we can infer that a vendor interacts with a vehicle or part in some way. In my original

version of this program, the mechanics at my local Township referred to vendors as both parts sellers

and repair shops at which vehicles were sent for maintenance activities. This gives vendors a similar role

as mechanics.

*Note: like for mechanics, I purposely limit the scope of a single maintenance activity to a sole vendor.

Also, the ‘may be performed by’ references the fact that a maintenance activity could be performed by

either a mechanic or a vendor. There is a chance that since both relationships use ‘may’, the entity

performing the maintenance could be left NULL. It would then be a responsibility of the third-party

application code to enforce an entry.

Additionally, vendors supply parts. If a part exists, it must have a vendor/source. A vendor could also

supply many types of parts, such as for a specific brand of vehicle. If a vendor is listed, they may or may

not sell parts since some vendors may only perform repairs.

Use Case #6: Appointment Entry

- The form asks the mechanic to select the vehicle by its vehicle ID code from a drop menu.

Entity #1

Participation

Relationship

Plurality

Entity #2

A vehicle

May

Contain

Many

Parts

A part

May

Be contained in

Many

Vehicles

Entity #1

Participation

Relationship

Plurality

Entity #2

A vendor

May

Perform

Many

Maintenance

Activities

A maintenance

activity

May

Be performed by

One

vendor

Entity #1

Participation

Relationship

Plurality

Entity #2

A vendor

May

Supply

Many

Parts

Part

Must

Be supplied by

One

vendor

Page 12 of 44

- The form asks for the mechanic to be assigned the maintenance activity from a drop menu.

- The form requires a maintenance due date to be filled in, as well as a checkbox to select if the

mechanic would like a one-week reminder sent to them.

- The form allows for the mechanic to put in a note regarding useful details about the

maintenance, such as type, ordered parts, etc.

The data entry from the steps above creates an appointment record, which the application processes

and in some manner calls status on to trigger an interaction with the mechanic. The database therefore

needs a table to log these appointment logs in. From the steps above, appointments establish a

relationship with mechanics and vehicles. In the terms of an appointment, an appointment is assigned

to one mechanic. However, a mechanic can be assigned many appointments as there may be many

vehicles. A mechanic does not have to be scheduled any appointments (may or may not), but if an

appointment exists, it must be given an appointee.

Similarly, nothing says that a vehicle needs to be assigned an appointment. A vehicle entity can exist

without an appointment assigned. Also, over time a vehicle may be assigned dozens of appointments. If

an appointment exists however, it must relate to one vehicle:

Derived from New Specialization Rule

- A maintenance activity is a vendor repair or an inspection or none of these.

Concerning the vendor repair created through the process of subtyping maintenance activity, we can

make the argument that a vendor repair will be performed by one vendor. A vendor can make multiple

repairs over time.

Derived from Normalization

Normalization of the Inspection subtype under MaintenanceActivity revealed a multitude of tests with a

pass, fail, ‘see comments’ repetition to them. A many to many relationship between inspections and

tests was revealed, and a second many to many relationship was found between tests and results:

Entity #1

Participation

Relationship

Plurality

Entity #2

A mechanic

May

Be assigned to

Many

Appointments

An appointment

Must

Be assigned to

One

mechanic

Entity #1

Participation

Relationship

Plurality

Entity #2

10a

A vehicle

May

Be assigned to

Many

Appointments

10b

An appointment

Must

Be assigned to

One

vehicle

Entity #1

Participation

Relationship

Plurality

Entity #2

11a

A vendor repair

Must

Be performed by

One

Vendor

11b

A vendor

May

Perform

Many

Vendor repairs

Entity #1

Participation

Relationship

Plurality

Entity #2

12a

An inspection

Must

Have

Many

Tests

12b

A test

May

Occur in

Many

Inspections

Page 13 of 44

Both will require bridge entities to resolve in the physical ERD.

Normalization of the Vehicle entity brought to light two additional sets of repetitions. The attribute

‘year’ would be repeated constantly, therefore it was extracted into its own entity set with a one-to-

many relationship with vehicle. Additionally, the ‘type’ attribute repeats one of the three options: CDL

vehicle, standard vehicle, or accessory. I gave ‘type’ its own entity with a one-to-many relationship with

vehicle as well.

Note: The Year to Vehicle entity relationship can be denormalized. Year may remain as an attribute of

the Vehicle entity.

Derived for History Table

- A PartHistory table will monitor the Parts table to track the history of quantity changes to

different parts over time.

As each part’s quantities will fluctuate with use, sometimes wildly, we know that a single part can have

multiple quantity changes. There is an extremely rare case where a part never gets used despite the

shop having quantity on the shelf, so we will use optional participation ‘may’. Looking in reverse, each

record in the PartHistory table reflects the quantity-at-time status of only one part. If the record exists,

a quantity change ‘must’ have occurred and likewise the part ‘must’ have existed.

The resulting PartHistory table will have a synthetic key ID, a foreign key back to the part it references,

the old part quantity, the new part quantity and the date of the change as attributes. The mechanism

for update will be the creation of a trigger that monitors any update activity on the Parts table where

changes in quantity have occurred.

Specialization / Generalization Rules

Supertype: Maintainer Subtypes: Mechanic, Vendor

Entity #1

Participation

Relationship

Plurality

Entity #2

13a

A test

Must

Have

Multiple

Results

13b

A result

Must

Occur in

Multiple

Tests

Entity #1

Participation

Relationship

Plurality

Entity #2

14a

A year

May

Apply to

Multiple

Vehicles

14b

A vehicle

Must

Associate with

One

Year

Entity #1

Participation

Relationship

Plurality

Entity #2

14a

A type

May

Apply to

Multiple

Vehicles

14b

A vehicle

Must

Have

One

Type

Entity #1

Participation

Relationship

Plurality

Entity #2

H-a

A part

May

Have

Multiple

Quantity changes

H-b

A quantity change

Must

Apply to

One

Part

Page 14 of 44

Justification

- Identified above in the vendor/maintenance activity and mechanic/maintenance activity

relationships, vendor and mechanic may function the same. A mechanic can work on a vehicle

in-house, or a vendor can work on a vehicle sent out to their remote garage. We can generalize

an entity supertype that encapsulates these two entities as subtypes and call it ‘Maintainer’.

Completeness

- In terms of the database, we only want maintenance activities performed by either a mechanic

or a vendor. We do not support any third-party option, as anyone not a mechanic can be

configured in the application program as a ‘vendor’ of service if need be. Therefore, the

relationship should be totally complete.

Disjointedness

- There is no case where an outside vendor would also be an in-house mechanic in this program.

Regarding the local municipality application, it is not a good practice for a mechanic on hourly

payroll to take money as a vendor after hours or moonlighting to perform a vehicle maintenance

for the municipality. As such, the subtypes will be disjoint.

Supertype: Appointment Subtypes: Automatic Appointment, Manual Appointment

Justification

- In the appointments use case, it states that an appointment can either be created manually by a

mechanic who fills in the details, or auto generated by the application based on some monitoring

factor. Common flags for automatic appointments are mileage (3,000 to 5,000 miles from last oil

change flags another maintenance), and inspection date (starting 3 months prior to a vehicle’s

inspection deadline).

Completeness

- These two subtypes will be the only types of appointment made by this program. Therefore, we

will say it is totally complete.

Disjointedness

- An appointment cannot be manual and automatic at the same time. Either the application

creates it or a mechanic does.

Supertype: Vehicle Subtypes: CDL Vehicle, Standard Vehicle, Accessory

I have decided to remove the subtypes previously associated with Vehicle. When considering the need

to extract out CDLVehicle, StandardVehicle and Accessory, I found that none will require unique

attributes not already found in the supertype. Therefore, upon implementation I will only use vehicle

but add in a ‘vehicle_type’ attribute which will discern the types of vehicles.

Supertype: Maintenance_activity Subtypes: Vendor_repair, Inspection

Justification

Supertype

Subtype #1

Subtype #2

Disjointedness?

Completeness?

A maintainer

A mechanic

Or a vendor

Supertype

Subtype #1

Subtype #2

Disjointedness?

Completeness?

An appointment

An automatic

appointment

Or a manual

appointment

Page 15 of 44

- In reviewing my attributes for week 4, I realized that there are a few different styles of

maintenance activities that are significantly different from the rest. For instance, an Inspection

should have fields that check tire treads, brakes, engines, lights, etc. that are all part of a state

inspection requirement. Normal maintenance might not go into that level of detail. Also, a

vendor repair might have repair warranty information or repair invoice linking number not

otherwise found in maintenance activities.

Completeness

- The scenario I’m envisioning is that most of the activities will be standard maintenance activities,

handled by the supertype. Occasionally a maintenance will be an inspection or vendor repair,

which will be handled by the subtypes. Thus, the relationship is partially complete, allowing for

optional subtype membership.

Disjointedness

- For the purposes of the database, a maintenance activity should be standard (the supertype

itself), a vendor repair or an inspection. Since maintenance activities are performed my

maintainers, and maintainers may include vendors, this takes care of the possibility an inspection

could be done by an outside vendor, which is rare. If we get into the weeds concerning shared

repairs, those are two different maintenance activities as viewed by the program. As such, the

subtypes are disjoint.

Here are my final structural database rules:

Associative Database Rules

- A vehicle make may apply to many vehicles; each vehicle must have one vehicle make.

- A vehicle model may correspond to many vehicles; each vehicle must correspond to one

vehicle model.

- A maintenance activity must apply to one vehicle; each vehicle may have many maintenance

activities.

- A maintenance activity may use many parts; each part may be used in many maintenance

activities.

- A vehicle may contain many parts; each part may be contained in many vehicles.

- A vendor may supply many parts, each part must be supplied by one vendor.

- A mechanic may be assigned to many appointments; each appointment must be assigned to

one mechanic.

- A vehicle may be assigned to many appointments, each appointment must be assigned to one

vehicle.

- A vendor repair must be performed by one vendor; each vendor may perform many vendor

repairs.

- An inspection must have many tests; each test may occur in many inspections.

- A test must have many results; each result must occur in many tests.

- A year may apply to multiple vehicles; each vehicle must associate with one year. (Removed)

- A type may apply to multiple vehicles; each vehicle must have one type.

- A part may have multiple quantity changes; each quantity change must apply to one part.

Supertype

Subtype #1

Subtype #2

Disjointedness?

Completeness?

A maintenance

activity

A vendor repair

Or an

inspection

Or none of

these.

Page 16 of 44

By nature of the generalization rule to follow, the mechanic/maintenance activity and

vendor/maintenance activity can now be replaced by maintainer/maintenance activity. Note,

maintenance activity previously had an optional relationship toward both mechanic and vendor. This

was to allow for the fact that it may be a mechanic or a vendor doing the maintaining. With the creation

of the Maintainer supertype, it now encapsulates all possible performers of the maintenance, so a

maintenance activity’s relationship toward maintainer is mandatory.

- A mechanic may perform many maintenance activities; each maintenance activity may be

performed by one mechanic. (Removed)

- A vendor may perform many maintenance activities; each maintenance activity may be

performed by one vendor. (Removed)

- A maintainer may perform many maintenance activities, each maintenance activity must be

performed by one maintainer.

Specialization / Generalization Database Rules

- A maintainer is a mechanic or a vendor.

- An appointment is an automatic appointment or a manual appointment.

- A vehicle is a CDL vehicle, a standard vehicle, an accessory, or none of these. (Removed)

- A maintenance activity is a vendor repair or an inspection or none of these.

Page 17 of 44

MET CS 669 Database Design and Implementation for Business

Term Project Iteration 6

Conceptual Entity-Relationship Diagram

CS 669 O2 – Term Iteration #4 Conceptual ERD, Ed Myers

Page 18 of 44

MET CS 669 Database Design and Implementation for Business

Term Project Iteration 6

Associative Database Rules

- A vehicle make may apply to many vehicles; each vehicle must have one vehicle make.

- A vehicle model may correspond to many vehicles; each vehicle must correspond to one vehicle

model.

- A maintenance activity must apply to one vehicle; each vehicle may have many maintenance

activities.

- A maintenance activity may use many parts; each part may be used in many maintenance activities.

- A vehicle may contain many parts; each part may be contained in many vehicles.

- A vendor may supply many parts, each part must be supplied by one vendor.

- A mechanic may be assigned to many appointments; each appointment must be assigned to one

mechanic.

- A vehicle may be assigned to many appointments, each appointment must be assigned to one vehicle.

- A maintainer may be assigned to many appointments; each appointment must be assigned to one

maintainer.

- An inspection must have many tests; each test may occur in many inspections.

- A test must have many results; each result must occur in many tests.

- A type may apply to multiple vehicles; each vehicle must have one type.

- A part may have multiple quantity changes; each quantity change must apply to one part.

Specialization / Generalization Database Rules

- A maintainer is a mechanic or a vendor.

- An appointment is an automatic appointment or a manual appointment.

- A maintenance activity is a vendor repair or an inspection or none of these.

From previous instances of the conceptual ERD, the following changes have been made:

- The addition of the Appointment entity, along with the expansion of its subsets

Automatic_appointment and Manual_appointment. The relationship is {mandatory, or} as an

appointment must be one of the two subtypes and cannot be more than one subtype at the same

time.

- The Vehicle entity is no longer expanded into subsets CDL_vehicle, Standard_vehicle and Accessory.

- The addition of Vehicle_make and Vehicle_model above Vehicle explain relationships between those

entities. These will be necessary tables within the database to reduce redundancy of data. The

relationships have been adjusted so that both relate back to the main Vehicle entity rather than each

other.

- The similarity between mechanics and vendors performing a maintenance activity was solved by

creating the generalization Maintainer. Maintainer now possesses the subtypes Mechanic and

Vendor. The relationship is {mandatory, or} as a maintainer must be classified as one of its subtypes

within the program, and a mechanic cannot be a vendor at the same time.

- With the addition of inspection, entities Test and Result were discovered in normalization. As their

relationships were Inspection (M->N) Test (M->N) Result, two more bridge entities InspectionTestList

and TestResult will be created in the physical ERD to reconcile.

- The Year entity has been collapsed back within Vehicle for denormalization.

- The PartHistory entity has been added with a (1->M) relationship to the part entity per the new rule.

Page 19 of 44

MET CS 669 Database Design and Implementation for Business

Term Project Iteration 6

Full DBMS Physical ERD

CS 669 O2 – Term Iteration #4 Physical ERD, Ed Myers

Page 20 of 44

MET CS 669 Database Design and Implementation for Business

Term Project Iteration 6

Justification of Attributes

Base Entity

Attribute

Data Type/Precision

Reasoning

Maintainer

maintainer_id

DECIMAL(3) PK

Adequate synthetic PK for maintainer

maint_type

VARCHAR(8)

Allows for entry ‘mechanic’ or ‘vendor’

maint_phone_num

VARCHAR(14)

‘x-xxx-xxx-xxxx’ format, including dashes

maint_email

VARCHAR(30)

Captures most long emails

Maintainer is a supertype that holds common attributes for the subtypes ‘Mechanic’ and ‘Vendor’. The

PK maintainer_id is mandatory, as is type of maintainer and phone number. Maintainer_id is 3 long as I

don’t anticipate more than 999 mechanics/vendors in this database. These should be required to

identify who is doing the maintaining and how to get a hold of them. Maintainer email is nullable only

because there are a select few who do not use email. Maintainer_id is a synthetic primary key.

(maint_first_name, maint_last_name) should not be considered a composite candidate key as two

individuals or vendors. No determinants exist that are not candidate keys. Maintainer is in BCNF.

Mechanic

maintainer_id

DECIMAL(3) PK,FK

Subtype of Maintainer

hire_date

DATE

Establishes seniority and experience of

mechanic

mech_first_name

VARCHAR(25)

Captures a variety of first names

mech_last_name

VARCHAR(25)

Captures a variety of last names

insp_license

VARCHAR(12)

given 12 characters for letter/number

formatting and multi-class licenses

Mechanic is a subtype of Maintainer, which holds the common attributes. The mandatory fields here

are maintainer_id, mech_first_name and mech_last_name. The other variables are nullable, as

hire_date is not especially necessary except for inter-personnel uses and some mechanics do not have

or are working toward acquiring an inspection license. Maintainer_id is the only primary key, and all

other attributes rely on it to map back to a specific mechanic. Mechanic is in BCNF.

Vendor

maintainer_id

DECIMAL(3) PK,FK

Subtype of Maintainer

ven_name

VARCHAR(25)

Name of vendor

ven_street_num

DECIMAL(6)

Street number of vendor address

ven_street

VARCHAR(30)

Street name of vendor address

ven_city

VARCHAR(20)

City name of vendor address

ven_state

CHAR(2)

State 2-char abbreviation of address

ven_zip

VARCHAR(10)

Allows for ‘xxxxx-xxxx’ zipcode format

ven_insurance_policy

VARCHAR(25)

Township requests proof of insurance

from vendors who we work with.

Allows for multiple character types

ven_agent

VARCHAR(64)

Personal or regional representative of

the vendor

Page 21 of 44

Vendor is a subtype of Maintainer, which holds the common attributes. Maintainer_id, name, street

number, street name, city, state, and zip are all mandatory. Insurance policy is nullable as a vendor may

be large and reputable enough not to require an on-hand policy id. We also may not have a dedicated

vendor assigned as agent. A note here about NF: there will be inevitable redundancy most likely with

city, state and zip. I recognize to attain BCNF these should be extracted out to remove repetition. I

made a design choice to keep them the way it is given the limited number of vendors used with a nod

to efficiency.

Appointment

appointment_id

DECIMAL(12) PK

Synthetic PK for appointment

vehicle_id

DECIMAL(5) FK

FK to vehicle entity

maintainer_id

DECIMAL(3) FK

FK to maintainer entity

appt_date

DATE

Date of appointment

appt_notes

VARCHAR(128)

Ample space for a decent note for why

the appointment was scheduled.

Appointment is a supertype to AutomaticAppointment and ManualAppointment. The PK and FK values

are mandatory, as well as the appointment date and notes. An appointment would be useless without a

date to apply to and a reason to have it. Appointment_id is the sole determiner of the appointment to

which all other attributes tie to in describing an appointment instance. Appointment is in BCNF.

Automatic-

Appointment

appointment_id

DECIMAL(12) PK,FK

Subtype of Appointment

trigger_mileage

BIT

Trigger flag that application sets/checks

to generate an automatic appointment.

An application event triggers code that

looks at the mileage from a vehicle’s

current activity and compares it to the

vehicles last mileage attribute. Given

the mileage difference and the vehicle

type, the application set the flag

appropriately.

trigger_insp

BIT

Trigger flag that application sets/checks

to generate an automatic appointment.

The application checks the vehicle’s

scheduled inspection date against the

current date to determine if it is ready

to set this flag off.

AutomaticAppointment is a subtype of Appointment, which holds the common attributes. The two

triggers are single BIT flags meant to act as Booleans for the application and are mandatory. If an

automatic appoint fires off, it should have one or both of these flags set to 1 to populate the note

attribute message properly upon appointment generation. Appointment_id is the sole PK, with the

other attributes depending on it, thus AutomaticAppointment is in BCNF.

Manual-

Appointment

appointment_id

DECIMAL(12) PK,FK

Subtype of Appointment

Page 22 of 44

appt_requester

VARCHAR(64)

Holds person or entity responsible for

requesting the maintenance, if not the

maintainer themselves.

ManualAppointment is a subtype of Appointment, which holds the common attributes. The

appt_requester attribute is mandatory as there must be a person responsible for initializing the request

if not automatic. The supertype appointment_id is the only determinant in the entity table, thus

ManualAppointment is in BCNF.

Part

part_id

DECIMAL(12) PK

Synthetic PK for part

part_name

VARCHAR(25)

Short part name identifier

maintainer_id

DEICMAL(3) FK

FK to maintainer entity

part_quantity_on_

hand

DECIMAL(4)

Quantity of part in stock. Not expecting

beyond 9999 of any part in stock.

part_quantity_units

VARCHAR(12)

Units describing part quantity (gals,

pieces, tubes, quarts, etc.)

part_description

VARCHAR(64)

Short description of part and what its

for

part_serial_number

VARCHAR(25)

Alphanumeric ID code usually vendor-

dependent for ordering supply.

Part contains all attributes used to identify a vehicle part and where its used. Mandatory attributes are

part_id, part_name and the foreign_key maintainer_id, which is used to identify which vendor the part

came from. Quantity on hand is also required so that the application can show how much of a part is in

stock during a maintenance activity entry. The application deducts the number used from this quantity

to keep a running total. Units to qualify the quantity are also required. Part description and serial

number are not required, as something like a bolt or nut may be self-explanatory and not have a proper

vendor number. Part_serial_number may act as a candidate key to Part, but is unreliable as you are

taking one vendor’s numbering convention against the other vendors’ conventions and hoping there

isn’t a uniqueness violation. Thus, part_id is the sole reliable determinant and Appointment is in

BCNF.

Maintenance-

Activity

maintenance_-

activity_id

DECIMAL(12) PK

Synthetic PK for the maintenance

activity

maintainer_id

DECIMAL(3) FK

FK linking back to the maintainer

vehicle_id

DECIMAL(5) FK

FK linking back to the vehicle

vehicle_mileage

DECIMAL(7)

Mileage input for the maintenance

entry

maint_type

VARCHAR(25)

Maintenance type, either “Standard

Maintenance” (supertype), “Vendor

Repair” or “Inspection”

maint_date

DATE

Date of maintenance activity

maint_total_cost

DECIMAL (7,2)

Total cost of parts for that specific

activity

Maintenance activity has the required fields, maintenance_activity_id, maintainer_id, vehicle_id, and

maint_date. Vehicle mileage is nullable since a maintenance may be on a piece of equipment where

Page 23 of 44

mileage isn’t tracked or a mileage appointment flag is undesired. If maint_type is left null, then the

application assumes the activity is a standard maintenance (supertype). Cost is nullable if no parts or

materials were used for the maintenance or of such low quantity that it was not tracked.

Maintenance_activity_id is the only determinant of the MaintenanceActivity entity, with all other

attributes relying on it to describe the activity instance. Thus, MaintenanceActivity is in BCNF.

VendorRepair

maintenance_-

activity_id

DECIMAL(12) PK,FK

Subtype of maintenance activity

repair_warranty_-

policy

VARCHAR(20)

Work guarantee policy identifier, if it

exists.

repair_invoice_ticket

VARCHAR(12)

Invoicing number for document

tracking. Actual cost should be entered

into the maint_total_cost in the

supertype.

maintainer_id

DECIMAL(3) FK

FK linking back to the maintainer

VendorRepair has maintenance_activity_id and maintainer_id as mandatory fields. The first is a

synthetic PK, while the second satisfies the fact that a vendor repair requires linking with a known

vendor. Warranty information may or may not exist depending on the type of vendor repair, similar to

an invoicing ticket. The maintenance_activity_id PK is the only determinant that describes the entire

VendorRepair instance, thus VendorRepair is in BCNF.

Inspection

maintenance_-

activity_id

DECIMAL(12) PK,FK

Subtype of maintenance activity

insp_comments

VARCHAR(1024)

Ample room for inspection comments

The Inspection entity originally took a form that possessed many tests and attributes. The process of

normalization created two more entity tables, Test and Result. These tables to be explained below,

handled the realization that Inspection (M->N) Test, and Test (M->N) Results. Thus two bridge entities,

InspectionTestList, and TestResult were created to handle those cases. Maintenance_activity_id is the

only determinant for Inspection, thus Inspection is in BCNF.

Test

test_id

DECIMAL(3) PK

Synthetic PK for the test activity

test_desc

VARCHAR(64)

Modest explanation of the test

The Test entity builds out the unique test table which InspectionTestList pulls from to generate unique

inspections. All fields are mandatory. As test_id is the sole determinant of the entity, Test is in BCNF.

Inspection_-

Test_List

inspection_test_-

list_id

DECIMAL(12) PK

Synthetic PK for the InspectionTestList

bridge entity

test_id

DECIMAL(3) FK

FK linking back to test

maintenance_-

activity_id

DECIMAL(12) FK

FK linking back to the inspection

(subtype of maintenance activity)

The sizing of test_id relates to there being less than a thousand tests in practicality that can apply to an

inspection. Inspection_Test_List is a bridge entity using FKs to link back to establish its 1-M

relationships. I’ve given it a synthetic PK for tracking, and since it is the only determinant it is in BCNF.

Page 24 of 44

Result

result_id

DECIMAL(1) PK

Synthetic PK for the result

result_desc

VARCHAR(12)

‘pass’, ‘fail’, ‘see comments’

As there are only three options for result (pass, fail, see comments), those options can be represented

using one decimal value. Result has one PK as a determinant, thus it is in BCNF.

TestResult

test_id

DECIMAL(3) PK,FK

PK bridge, FK linking back to the test

result_id

DECIMAL(1) PK,FK

PK bridge, FK linking back to the result

TestResult is a bridge entity that I am not planning on tracking, so I will leave it to be populated only by

its foreign keys. Thus TestResult is in BCNF.

PartEntry

part_entry_id

DECIMAL(12) PK

Synthetic PK for part entry

maintenance_-

activity_id

DECIMAL(12) FK

FK linking to maintenance activity

part_id

DECIMAL(12) FK

FK linking back to part

part_amount_used

DECIMAL(3)

Quantity of part used

part_amount_units

VARCHAR(12)

Units that part quantity is measured in

I made a synthetic PK for PartEntry even though it is a bridge entity between Part and

MaintenanceActivity as it additionally tracks amount used. Part_amount_used is of DECIMAL(3) size

because I can’t see using more than 999 of a part or material in a single maintenance activity. Part_id is

the sole determinant, therefore PartEntry is in BCNF.

VehiclePartList

vehicle_id

DECIMAL(5) PK,FK

PK bridge, FK linking back to vehicle

part_id

DECIMAL(12) PK,FK

PK bridge, FK linking back to part

VehiclePartList is a bridge entity possessing two foreign keys. I do not intend to track it with a primary

key as it holds no other additional data. Therefore it is in BCNF.

Vehicle

vehicle_id

DECIMAL(5) PK

Synthetic PK for vehicle_id

vehicle_model_id

DECIMAL(6) FK

FK linking to vehicle model entity

vehicle_shop_code

DECIMAL(5)

In-house vehicle code number

vehicle_make_id

DECIMAL(4) FK

FK linking to vehicle make entity

vehicle_year

DECIMAL(4)

Holds year value for vehicle year

vehicle_license

VARCHAR(8)

Vehicle license plate value

vehicle_vin

VARCHAR(17)

Vehicle Identification Number, created

by the manufacturer

vehicle_type_id

DECIMAL(1) FK

FK linking to the type entity

veh_last_mileage

DECIMAL(7)

Last mileage recorded for the vehicle

veh_schd_insp_date

DATE

Scheduled upcoming inspection date

In normalizing the Vehicle entity, repeated values were found with the ‘year’ and ‘type’ attributes. As

these were transitive dependencies, I’ve split them out into their own entities with many-to-one

relationships back to the vehicle. The vehicle_ID is a synthetic key, but the vehicle shop code is an

internal numeric id using the naming convention of the municipality (ex./ 4002 is the 2

vehicle within

Page 25 of 44

the highway fleet, identified by the leading 4). It is allowed because it acts like a candidate key. Vehicle

license and VIN are sized appropriately to their length. Vehicle last mileage and vehicle scheduled

inspection date are used by the appointment entity as checks to determine if a mileage or inspection

appointment is needed. All transitive dependencies have been removed from Vehicle to their own

entities. Vehicle_id remains the PK primary determinant. An argument can be made that the VIN due

to uniqueness is a candidate key. Even though this is allowed, it will not be used for indexing within

this application. Therefore Vehicle is in BCNF.

VehicleModel

vehicle_model_id

DECIMAL(6) PK

Synthetic PK for vehicle model

model_name

VARCHAR(25)

Description of model

Vehicle_model_id and model_name are sized within reason for their uses. I went a little large with

model, but seeing as the uses may extend beyond traditional vehicles and into other equipment, its

better to be safe. Vehicle_model_id is the only PK determinant, therefore VehicleModel is in BCNF.

VehicleMake

vehicle_make_id

DECIMAL(4) PK

Synthetic PK for vehicle make

make_name

VARCHAR(25)

Description of make

Same idea and description as for VehicleModel. Vehicle_make_id is the only PK determinant, therefore

VehicleMake is in BCNF.

VehicleYear

vehicle_year_id

DECIMAL(2) PK

Synthetic PK for year

vehicle_year

DECIMAL(4)

Holds year value for vehicle year

Vehicle Year entity has been removed and folded into Vehicle by denormalization.

VehicleType

vehicle_type_id

DECIMAL(1) PK

Synthetic PK for type

vehicle_type

VARCHAR(25)

‘CDL Vehicle’, ‘Standard Vehicle’ or

‘Accessory’

There are only three anticipated types as listed, so the type_id having one decimal satisfies the indexing

need. In the event more are created in the future, six index placement remain. Vehicle type_id is the

only PK determinant, therefore VehicleType is in BCNF.

PartHistory

part_history_id

DECIMAL(12) PK

Synthetic PK for part history

part_id

DECIMAL(12) FK

FK linking to part entity

part_old_quant

DECIMAL(4)

Old, replaced quantity value

part_new_quant

DECIMAL(4)

New quantity value

part_change_date

DATE

Date of quantity change

This entity serves as a history table to track quantity changes of parts over time. All fields are

mandatory. Part_history is sized to reflect the possibility of there being many part quantity changes in

the history table, and part_id’s sizing reflects the key it references in Part. The old and new quantity

fields are sized the same as the active quantity field within the Part entity. As part_history_id is the

only PK and no other attribute can serve as a candidate key to the group, PartHistory is in BCNF.

Page 26 of 44

The DBMS physical ERD greatly resembles the conceptual ERD created and updated earlier. Differences of

note:

- Two bridge entities had to be created to deal with the M:N relationships between Maintenance

Activity/Part and Part/Vehicle.

o PartEntry represents the bridge for the first set, which makes sense if you think of a

maintenance entry initiating a unique part entry into a maintenance activity record for each

part used per each separate maintenance. PartEntry contains the foreign keys for both

MaintenanceActivity and Part.

o VehiclePartList reconciles the relationship for the second set, which works because each

individual vehicle should have a customized part list attached to it, regardless of whether a

part on the list could fit in a different vehicle. VehiclePartList contains the foreign keys for

both Vehicle and Part.

- From the conceptual ERD, subtypes VendorRepair and Inspection were added to specify the

supertype MaintenanceActivity. This spawned an additional one to one relationship between

Vendor and VendorRepair, as for our case only one vendor can make a repair that counts as a

single maintenance activity.

- In the process of creating the Inspection entity, due to transitive repetition I found the need to

create a Test entity which represents the numerous tests an inspector would need to perform to

pass a vehicle’s inspection maintenance activity. Recursively, I found again that a test could have

multiple results, so I created a Result entity to hold the possibilities ‘pass’, ‘fail’, and ‘see

comments’.

- From the use cases built for these entities, I found that Inspection <-> Test and Test <-> Result were

both many-to-many relationships. This necessitated two bridge entities to be created, one called

‘InspectionTestList’ and the other ‘TestResult’, which solved the problems.

- From the normalization of Vehicle, I found that the attributes ‘year’ and ‘type’ were subject to

much repetition. Therefore I extracted them out of the Vehicle entity and created one-to-many

relationships between each back to vehicle.

- VehicleYear is now denormalized to become an attribute within Vehicle.

- Primary keys for VehiclePartList and TestResult have been created.

- Part_name was added as an attribute to the Part entity.

- PartHistory was added as a history tracking table with a 1-M relationship with Part. No bridge

entities are required.

Many of my relationships are 1:M, so where you see that type of relationship I have placed a foreign key

reference in the entity of the many side back to the entity ID of the one side.

Style-wise, I used the entity relationship structures within LucidChart rather than the stock UML class

shapes. I liked how each structure fit the key type, entity ID and data type in a more organized way.

Page 27 of 44

Stored Procedure Execution and Explanations

Use Case: Add Vehicle

The scenario exists where a user needs to enter a new vehicle into the program application. A refinement

of the Add Vehicle use case is given below:

1) The mechanic clicks the ‘Add Vehicle’ button on the application interface.

2) The application provides a fill form to accept the data fields.

model

The model of the vehicle

Only certain parts work on certain vehicles.

vehicle ID

(shop code)

The 4-digit departmental

ID code of the vehicle

This is the naming convention already used, and

uniquely identifies the vehicle.

make

The brand of the vehicle

Only certain parts work on certain vehicles.

year

The creation year of the

vehicle

The year of the vehicle is the primary statistic used to

determine parts availability and to evaluate

replacement. The local township participates in a fleet

lease program in which a mixed trade in of vehicles 3

years and older reduce the overall fleet age to save on

maintenance costs.

license

The license plate value

You can check which vehicles need renewed registration

and pull status of insurance.

VIN

The auto manufacturer

vehicle identification

number

The coding of the VIN gives exact vehicle specifications,

and the VIN is required on nearly all types of

registration paperwork.

vehicle type

The type of vehicle, being

a CDL vehicle, standard

vehicle, accessory, or none

This is a new attribute created for the

generalization/specialization rule set. Depending on the

type of vehicle, different information will be available

and different check flags will apply to appointments.

last mileage

The last recorded mileage

of the vehicle

This is used as a comparison with a new mileage

entered from latest activity to throw a flag that triggers

an automatic appointment to be created by the

program if satisfied.

Inspection

date

The date the vehicle is due

for its next inspection

The inspection date is important to keep the vehicle

street-legal, and serves as a parameter by which the

application’s appointment notifier communicates a

necessary vehicle maintenance activity to the mechanic.

3) The mechanic specifies the type of vehicle as CDL vehicle, standard vehicle, accessory, or none.

4) The mechanic enters all other the appropriate information and clicks ‘Save’.

The stored procedure accepts the vehicle model, shop code, make, year, license, VIN, type (CDL Vehicle,

Standard Vehicle, or Accessory) mileage and next inspection date. The procedure attempts to be robust by

blocking null variables, out of range numeric values and unrealistic years/dates. It also prevents duplicate

inputs of the VIN and shop code, which are the closest things to alternate candidate keys.

Page 28 of 44

The core Vehicle entity possesses four foreign key dependencies to lookup tables, so those tables must be

sequenced appropriately. To maintain uniqueness, each lookup table insertion first checks for a duplicate

entry prior to creating a new sequence id. If none is found, it creates a new entry. Lastly, when entering

all information as an insertion into the Vehicle entity, all sequence IDs for the foreign keys must be looked

up by subquery.

Here is my stored procedure definition using SQL Server:

Page 29 of 44

Here is execution of the transaction along with the initial entry of the appropriate tables:

Page 30 of 44

Use Case: Add Vendor (Vendor is a subtype of Maintainer)

Upon setup of the original application, a user will need to register an in-house mechanic or an outside

vendor into the system for them to be selectable in the maintenance activity entry screen. For this

example, the user wants to set up a new vendor. A refinement of the Add Vendor use case is given below:

1) The mechanic clicks the ‘Add Vendor’ button on the application interface.

2) The application provides a fill form to accept information regarding the vendor.

Field

What it Stores

Why it’s Needed

vendor_name

The vendor’s name

This helps to track which vendor performed

what work for workload analysis.

street number

Vendor address variables

This helps identify where the vendor is located.

Proximity can be a factor for vendor repair

selection.

street name

town/city

state

zip code

phone_number

Phone number variables

This stores telephone contact information for

the vendor.

agent_name

Vendor representative, if

applicable

This is useful in case the vendor has an area

sales representative that receives business from

your area.

insurance policy

Insurance policy number if

applicable

A place to store proof of insurance carried by

vendor for the work that they do.

Email contact address

Similar to phone number, it is another way to

get in contact with the vendor.

3) The mechanic enters the data about the vendor.

4) The mechanic presses ‘Save’ to save the entry.

This procedure takes the vendor name, street number, street name, city, state, zip-code, phone number,

insurance policy number (if given), agent name (if given), and email (if given). The last three parameters

are optional as determined by the Vendor subtype and Maintainer supertype entities.

Preliminary checks for null values in the mandatory fields are applied. A check for a less than zero street

number and already-existing vendor name is also applied. The attributes associated with the Maintainer

Page 31 of 44

supertype are inserted first to maintain the foreign key constraint, then exclusive attributes to the Vendor

subtype are inserted.

Here is my stored procedure definition using SQL Server:

Here is execution of the transaction along with the initial entry of the appropriate tables:

Page 32 of 44

Question Identification and Explanations

Scenario: Vehicle Trade-Ins

Query 1: How many vehicles qualify for trade in through the municipal lease program? Select all vehicles

with 5,000 miles or greater and are more than 3 years old. Show how many distinct types of vehicles

apply, giving the make, model and type in the result. Order the results descending by mileage and give a

count of how many vehicles are within each output row and percentage of the overall fleet.

An administrator of the vehicle fleet wants information regarding how many vehicles are eligible to be

traded in for new ones per a lease agreement. A municipality can enter a lease agreement with a vehicle

supplier to prevent the average age of its vehicles from getting too old. As a vehicle gets older, it often

takes more time, parts, fluids, and money to maintain. To prevent the ‘nickel and dime’ effect, it is

advantageous to have a replacement plan to save on maintenance costs. Moreover, knowing the make

and model of the aging vehicles within the fleet will help the administrator identify purchasing trends.

Knowing the percentage of vehicles that are tradeable out of the whole allows the administrator to

accurately plan how many replacement leases they should schedule in the next few budget years. Type of

vehicle is important since ‘CDL Vehicles’ are usually more expensive to replace given their size, capacity

and special registration.

Scenario: Ordering Parts from a Vendor

Query 2: Generate a list of parts that have a quantity 10 ‘units’ or under. List them in a results table

alongside the vendor which sells the part, their phone number, email if known and agent if known. Due to

intra-state shipping surcharges, administration doesn’t want anyone purchasing parts from out-of-state

(PA in my case). Disqualify any parts from the list which have vendors outside of PA.

The in-house mechanic wants to stock the parts room for the month and would like to replenish materials

that are running low in the shop. This is a standard supply ordering activity that can happen per week or

per month in a mechanic’s shop. Its very useful to know not only what you are running low on, but the

right contacts to order the supplies from. I have personally seen a purchasing freeze similar to this

scenario, and everyone can relate to delivery surcharges related to fuel cost increases.

Scenario: Monthly Inspection List

Query 3: Generate a list of how many vehicle inspections are due in which months. There should be 12

results, one for each month with the number of inspections given in each month, even if there are none.

The list should be chronologically correct in order of month. CDL Vehicle inspections run on a 6-month

interval, where Standard Vehicle inspections run on a 12-month interval.

The in-house mechanic runs a report at the beginning of the year which forecasts how many vehicle

inspections are due and on what month they land. This is a workload query to judge proper time

management in forecasting what inspections land where. As inspections can be shifted within a 3-month

window prior to the deadline, the mechanic can use this report to balance out the workload on the books

if needed.

Page 33 of 44

Query Executions and Explanations

Here is a screenshot of Query #1:

To achieve the results, I joined the Vehicle table to the VehicleMake, VehicleModel and VehicleType

tables by foreign key ties. I then chose aggregate functions to perform an average mileage calculation,

count, and fleet percentage on the returned rows. I excluded any vehicle entries with less than 5,000

miles and less than 3 years old. I grouped the results by common vehicle make, model and type, ordered

by average mileage descending.

I created a proofing query to demonstrate that Query #1 worked as intended. Below is a simple query and

table generated to output the year, make model, type and mileage of all vehicles inserted into the Vehicle

table:

Page 34 of 44

As a sample, there are two vehicles of the type ‘Chevrolet Silverado, Standard Vehicle’. Both have mileage

greater than 5,000 (35,234 and 23,344 respectively) and are both over 3 years old (1997 and 2016

respectively). This would create a single row when grouped by make and model. The count would be 2 as

there are two vehicles of that style, and the average mileage would be (35,234 + 23,344) / 2, or 29,289.

There are a total of 11 vehicles in the fleet listed, so the percentage of qualified Chevy Silverados would be

2 / 11 or 18.18%. All values are correct.

Here is a screenshot of Query #2

To achieve the results, I joined a populated Parts table to Vendors which connected the parts to the

vendors which sold them. Next, I joined the Vendor table (which is a subtype) to the Maintainer table

(the supertype) to connect related attributes between the two. From numerous tables I specified

attributes that gave me part name, quantity, supplying vendor, phone number, email if known and agent if

known. Entries in which part quantities were greater than 10 or the suppliers were outside of PA were

parsed out.

To validate the above result, I’ve created a proofing query which outputs all parts in the database,

matched up with their supplying vendor and pertinent information:

Page 35 of 44

From the above output, the ‘WD-40’ part entry is invalid because its supplying vendor ships from NY. ‘Lug

Nut’ and ‘Oil, 30W’ are out because their quantities in stock are above 10. The last two remaining parts

qualify for restock, which are ‘Windshield Wiper’ and ‘S32 Hydraulic Oil Filter’. These are the values

returned from my query; thus it is correct.

Here is a screenshot of Query #3:

To achieve the results, within my view I decided to use an aggregate COUNT() function to count the

number of inspections that fell within each month upon grouping. I created an inline SELECT pseudo

lookup table to associate month names with month id numbers. I performed a LEFT JOIN with a UNION

ALL of two tables derived from the Vehicle table. The first half of a union was a second JOIN between the

inspection date attribute of the Vehicle table where values returned were applicable to vehicles of the

‘CDL Vehicle’ type via a WHERE clause. I modified the values here to return the remainder of the (old

month + 6) / 12. This takes care of the fact that CDL vehicles have two inspections a year, 6 months apart.

The second half of the union was to the unaltered month values of the original vehicle list. The purpose of

this union was to include one additional inspection per CDL Vehicle to acknowledge the fact that they get

two inspections per year. Multiple SUBQUERIES were used throughout.

Page 36 of 44

Outside the view, a simple query calls for the month and number of inspections, ORDERING by ascending

month value.

To validate the above result, I’ve created a proofing query which outputs all vehicles in the database,

matched up with vehicle type, inspection date and other pertinent information (such as month number for

convenience):

Across all vehicle entries, from the counting of the month numbers we can see that there are 3 in March, 2

in April, 2 in May, 2 in November and 2 in December. Going back and counting the ‘CDL Vehicle’ entries,

both have their original inspections in May. Adding 6 months onto these, we know that these two CDL

vehicles need inspected again in November. Adding two more inspections to November and assigning 0 to

the rest, we have:

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

This matches the output; therefore the view query is correct.

Page 37 of 44

Index Identification and Creations

From Primary Keys (already indexed)

Maintainer.maintainer_id

Inspection_Test_List.inspection_test_list_id

Vendor.maintainer_id

Result.result_id

Mechanic.maintainer_id

TestResult.test_id

Appointment.appointment_id

TestResult.result_id

AutomaticAppointment.appointment_id

PartEntry.part_entry_id

ManualAppointment.appointment_id

VehiclePartList.vehicle_id

Part.part_id

VehiclePartList.part_id

MaintenanceActivity.maintenance_activity_id

Vehicle.vehicle_id

VendorRepair.maintenance_activity_id

VehicleModel.vehicle_model_id

Inspection.maintenance_activity.id

VehicleMake.vehicle_make_id

Test.test_id

VehicleType.vehicle_type_id

From Foreign Keys (to be indexed)

Entity.Attribute

Unique?

Reason for Indexing

Appointment.vehicle_id

Not unique